Serendipity Effects in my Emails!!!

November 30, 2017 PL/SQL Email Selection test

A couple of years ago, I applied for an undergraduate program at Barry University. This morning I met a colleague reading a book about preparing for success in healthcare information and management systems. I have no idea about previous editions, but the third edition’s cover image gathers people of all races. Suddenly, I was a little bit curious to browse quickly the preface and author biographies. When I fell on one of the authors, Melissa Barthold. I discovered that she was an adjunct professor at Barry University in the School of Nursing. This reminded me of the application submitted to this University.

I started to search for all emails I sent that contain the word “Barry” and I got five results.

After clicking on the first one, I realized that the content was absolutely different from my expectation. Contrariwise, the keyword matched with the sender name. This email was my reply to the selection test that a company sent me. I was looking for A but I found B.

Find below my response to the question I was asked:

Question

Write a PL/SQL block which will populate the RESULTS table as described below. Consider performance implications (specifically where would you commit the queries) as we are dealing with millions of records. This query is suppose to cater for large volumes of data.

The PL/SQL must:

insert into the RESULTS table each customer id, and the number of unique products purchased by that customer.
update the recently_purchased column of the customer table to ‘Y’ (yes if they have purchased a product in the last 12 months) or ‘N’ (if they have not purchased a product in the last 12 months).

Listed below are the tables & definitions:

table: CUSTOMER columns: customer_id NUMBER, customer_name VARCHAR2(100), recently_purchased VARCHAR2(1) – ‘Y’ or ‘N’
table: CUST_PRODUCTS columns: product_id NUMBER, customer_id NUMBER, date_purchased DATE
table: RESULTS columns: customer_id NUMBER, product_count NUMBER
table: PRODUCTS column: product_id NUMBER, product_name VARCHAR2(100)

Solution

  DECLARE
            VARCHAR2 (100) := 'Recruitment and Selection Test of Jean Marie Cimula'; 
  BEGIN
    DBMS_OUTPUT.put_line (ENTERPRISE);
    
    ---insert into the RESULTS table each customer id, 
    ---and the number of unique products purchased by that customer.
    INSERT INTO RESULTS (customer_id,product_count)
    SELECT customer_id,COUNT(product_id) product_count 
    FROM (
             SELECT distinct customer_id,product_id
             FROM CUST_PRODUCTS
         )
    GROUP BY customer_id;
    COMMIT;
    
  ---update the recently_purchased column of the customer table 
    MERGE INTO CUSTOMER B
    USING( 
        SELECT customer_id, 
               CASE WHEN 
               ROUND(MONTHS_BETWEEN (SYSDATE,TO_DATE(date_purchased,'MM-DD-YYYY'))) <= 12 THEN 'Y' 
               ELSE 'N' END date_purchased
                FROM CUST_PRODUCTS             
           ) T
    ON( B.customer_id= T.customer_id) 
    WHEN MATCHED THEN UPDATE SET B.recently_purchased=T.date_purchased;
    COMMIT; 

  END;
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line 
   (DBMS_UTILITY.format_error_stack);
END;